package water.com.DaoSupport.DbBase;


import water.com.Aop.WaterSqlPrintHandler;
import water.com.util.Built;
import water.com.util.ConfigurationReader;
import water.com.util.WaterSQLGenerator;
import water.com.util.WaterSQLGeneratorImp;

import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * ORM基本实现层
 */
public class WaterDaoSupportImp implements WaterIDaoSupport<Object,Object> {

    private final String driver;
    private final String url;
    private final String userName;
    private final String password;
    private Statement stmt=null;
    private ResultSet rs=null;
    private Connection conn=null;
    private WaterSQLGenerator<Object> waterSqlGeneratorImp;

    @SuppressWarnings({"unchecked"})
    private WaterDaoSupportImp(){
        driver = (String)ConfigurationReader.getInstance().getValueByKey("dataBase","driver");
        url = (String)ConfigurationReader.getInstance().getValueByKey("dataBase","url");
        userName = (String)ConfigurationReader.getInstance().getValueByKey("dataBase","userName");
        password = (String)ConfigurationReader.getInstance().getValueByKey("dataBase","password");
        waterSqlGeneratorImp = new WaterSQLGeneratorImp<>();
        //增强SQL生成器，使其打印出生成的SQL语句
        InvocationHandler handler = new WaterSqlPrintHandler(waterSqlGeneratorImp);
        waterSqlGeneratorImp = (WaterSQLGenerator<Object>) Proxy.newProxyInstance(waterSqlGeneratorImp.getClass().getClassLoader(),
                waterSqlGeneratorImp.getClass().getInterfaces(),handler);

    }

    /**
     * 提供使用不同的SQL生成器进行SQL语句生成
     * @param waterSqlGeneratorImp SQL语句生成器
     */
    public void setWaterSQLGenerator(WaterSQLGenerator<Object> waterSqlGeneratorImp){
        this.waterSqlGeneratorImp = waterSqlGeneratorImp;
    }

    private static class SingletonHolder {
        private static final WaterDaoSupportImp instance = new WaterDaoSupportImp();
    }

    public static WaterDaoSupportImp getInstance() {
        return WaterDaoSupportImp.SingletonHolder.instance;
    }

    /**
     * 初始化连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private void initConnection() throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName(driver);
        //获得连接
        conn = DriverManager.getConnection(url,userName,password);
        //获得sql执行对象
        stmt= conn.createStatement();
    }

    /**
     * 单条数据通用查询方法实现，如果存在多条数据符合查询，则只返回第一条
     * @param model 表模型
     * @return 当查询不到数据时，返回null，查询到数据时返回其实例化并赋值的对象
     */
    @Override
    public Object selectOne(Object model) throws Exception{
        String Sql = null;
        boolean successFlag = false;
        Class<?> cl = model.getClass();
        try {
            //查询sql生成器
            Sql = waterSqlGeneratorImp.selectGenerator(model);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            rs = stmt.executeQuery(Sql);
            Field[] fields = cl.getDeclaredFields();
            AccessibleObject.setAccessible(fields,true);
            if(rs.next()){
                successFlag = true;
                for(Field field:fields){
                    field.set(model,rs.getString(Built.toColumnName(field.getName())));
                }
            }
            if(!successFlag){
                return null;
            }
        }catch(ClassNotFoundException | SQLException | IllegalAccessException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,rs,conn);
        }
        return model;
    }

    @Override
    public Object updateOne(Object model) throws Exception{
        int updateCount;
        try {
            String Sql = waterSqlGeneratorImp.updateGenerator(model);
            initConnection();
            conn.setAutoCommit(false);
            updateCount = stmt.executeUpdate(Sql);
            if(updateCount==1){
                conn.commit();
                return updateCount;
            }else {
                conn.rollback();
            }
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }

        return null;
    }

    @Override
    public Integer updateOneAllowNull(Object model, Map<String, String> conditionsMap) throws Exception{
        int updateCount;
        String Sql = null;
        try {
            Sql = waterSqlGeneratorImp.updateGeneratorAllowNull(model,conditionsMap);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            updateCount = stmt.executeUpdate(Sql);
            if(updateCount==1){
                conn.commit();
                return updateCount;
            }else {
                conn.rollback();
            }
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return null;
    }

    /**
     * 单条数据通用更新方法实现
     * @param model 表模型
     * @param conditionsMap 更新条件
     * @return 执行成功时，返回其影响的行数1，否则回滚且返回null
     */
    @Override
    public Integer updateOne(Object model, Map conditionsMap) throws Exception{
        int updateCount;
        String Sql = null;
        try {
            Sql = waterSqlGeneratorImp.updateGenerator(model,conditionsMap);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            updateCount = stmt.executeUpdate(Sql);
            if(updateCount==1){
                conn.commit();
                return updateCount;
            }else {
                conn.rollback();
            }
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return null;
    }

    /**
     * 单条数据删除通用实现（物理删除）
     * @param model 数据模型对象
     * @return 执行成功时，返回其影响的行数1，否则回滚且返回null
     */
    @Override
    public Integer deleteOne(Object model) throws Exception{
        int deleteCount;
        String Sql = null;
        Class<?> cl = model.getClass();
        try {
            Sql = waterSqlGeneratorImp.deleteGenerator(model);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            deleteCount = stmt.executeUpdate(Sql);
            if(deleteCount==1){
                conn.commit();
                return deleteCount;
            }else {
                conn.rollback();
            }
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return null;
    }

    /**
     * 单条数据插入通用实现
     * @param model 数据模型对象
     * @return 执行成功时，返回其影响的行数1，否则回滚且返回null
     */
    @Override
    public Integer insertOne(Object model) throws Exception{
        int insertCount;
        String Sql = null;
        Class<?> cl = model.getClass();
        try {
            Sql = waterSqlGeneratorImp.insertGenerator(model);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            insertCount = stmt.executeUpdate(Sql);
            if(insertCount==1){
                conn.commit();
                return insertCount;
            }else{
                conn.rollback();
            }
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return null;
    }

    /**
     * 批量数据查询通用实现
     * @param model 入参数据对象模型
     * @return 当查询不到数据时返回null， 查询到数据时，将查询到的数据填入list中并返回
     */
    @Override
    public List selectList(Object model) throws Exception{
        List<Object> results=new ArrayList<>();
        String Sql = null;
        boolean successFlag = false;
        Class<?> cl = model.getClass();
        try {
            Sql = waterSqlGeneratorImp.selectGenerator(model);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            rs = stmt.executeQuery(Sql);
            Field[] fields = cl.getDeclaredFields();
            AccessibleObject.setAccessible(fields,true);
            while(rs.next()){
                successFlag = true;
                Object newModel = cl.newInstance();
                for(Field field:fields){
                    field.set(newModel,rs.getString(Built.toColumnName(field.getName())));
                }
                results.add(newModel);
            }
            if(!successFlag){
                return null;
            }
        }catch(ClassNotFoundException | SQLException | IllegalAccessException | InstantiationException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,rs,conn);
        }
        return results;
    }

    /**
     * 批量数据更新通用实现，支持置空
     * @param list 要更新成目标状态的数据集合
     * @param conditionList 更新条件
     * @param isAllowNull true-支持置空  false-不支持置空
     * @return 返回执行成功的数量
     */
    @Override
    public Object updateList(List list,List conditionList,boolean isAllowNull) throws Exception{
        int num = 0;
        for(int i=0 ;i<list.size();i++){
            Integer one;
            if(!isAllowNull){
                one = updateOne(list.get(i),(Map)conditionList.get(i));
            }else {
                one = updateOneAllowNull(list.get(i),(Map)conditionList.get(i));
            }
            if(one!=null) num += one;
        }
        return num;
    }
    /**
     * 批量数据更新通用实现，不支持置空
     * @param list 要更新成目标状态的数据集合
     * @param conditionList 更新条件
     * @return 返回执行成功的数量
     */
    @Override
    public Object updateList(List list,List conditionList) throws Exception{
        int num = 0;
        for(int i=0 ;i<list.size();i++){
            Integer one;
            one = updateOne(list.get(i),(Map)conditionList.get(i));
            if(one!=null) num += one;
        }
        return num;
    }

    /**
     * 批量物理删除通用实现
     * @param list 需要删除的数据
     * @return 失败返回-3，成功返回-2
     */
    @Override
    public Object deleteList(List list) throws Exception{
        int [] insertCount;
        String Sql = null;
        List<String> sqlList = new ArrayList<>();
        try {
            for(Object model:list){
                Sql = waterSqlGeneratorImp.deleteGenerator(model);
                sqlList.add(Sql);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            for(String sql:sqlList){
                stmt.addBatch(sql);
            }
            insertCount = stmt.executeBatch();
            for(int i:insertCount){
                if(i==-3){
                    conn.rollback();
                    return -3;
                }
            }
            conn.commit();
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return -2;
    }

    /**
     *批量插入通用实现
     * @param list 需要插入的数据集
     * @return 失败返回-3，成功返回-2
     */
    @Override
    public Object insertList(List list) throws Exception{
        int [] insertCount;
        String Sql = null;
        List<String> sqlList = new ArrayList<>();
        try {
            for(Object model:list){
                Sql = waterSqlGeneratorImp.insertGenerator(model);
                sqlList.add(Sql);
            }
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
        try{
            initConnection();
            conn.setAutoCommit(false);
            for(String sql:sqlList){
                stmt.addBatch(sql);
            }
            insertCount = stmt.executeBatch();
            for(int i:insertCount){
                if(i==-3){
                    conn.rollback();
                    return -3;//代表失败
                }
            }
            conn.commit();
        }catch(ClassNotFoundException | SQLException e){
            e.printStackTrace();
            throw e;
        }finally {
            //关闭连接
            closeConnection(stmt,null,conn);
        }
        return -2;//代表成功
    }


    /**
     * 关闭连接及相应资源
     * @param stmt sql执行对象
     * @param rs 结果集对象
     * @param conn 数据库连接对象
     */
    public void closeConnection(Statement stmt,ResultSet rs,Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            rs = null;
        }

        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            stmt = null;

        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            conn = null;
        }
    }
}
